﻿using DHQGHN.DAL.Base;
using DHQGHN.DAL.Models.DHQG;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DHQGHN.DAL.DAO.DHQG
{
    public class KeHoachThucHienDAO: SqlDAO
    {
        public const string PROCEDURE_INSERT = "spKeHoachThucHien_Insert";
        public const string PROCEDURE_INSERT_BY_OPTION = "spKehoach_Thuchien_Update_By_Option";
        public const string PROCEDURE_Update = "spKeHoachThucHien_Update";
        public const string PROCEDURE_DELETE_BY_NAMKEHOAH = "spKehoach_ThucHien_DeleteByNKH";
        public const string PROCEDURE_CHECK_CHOT = "spKehoach_ThucHien_Checkchot";
        public const string PROCEDURE_GET_BY_NKH = "spKeHoach_Thuchien_GetByNamKeHoach";
        public const string PROCEDURE_CHECK_CHOT_MOC_KIEM_SOAT = "spKehoach_Thuchien_CheckChotbyDONVI";
        public const string PROCEDURE_GET_ALL = "spKehoach_ThucHien_Getall_by_nkh";
        public const string PROCEDURE_GET_Rieng_Getall_by_nkh = "spKehoach_ThucHien_Rieng_Getall_by_nkh";
        public const string PROCEDURE_GET_Rieng_Getall_by_mockiemsoat = "spKehoach_ThucHien_Getall_by_nkh_mockiemsoat";
        public const string PROCEDURE_GET_KHTH_BY_OPTION = "spKehoachThucHien_GetByOption";
        public const string PROCEDURE_HUY_CHOT = "spKehoach_Thuchien_HuychotbyDONVI";
        public const string PROCEDURE_GET_SUM = "spKehoach_ThucHien_GetByNamKeHoach_SUM";
        public const string PROCEDURE_CheckFeedBack = "spKehoach_ThucHien_CheckFeedBack";
        public const string PROCEDURE_CAP1_GET_SUM = "spKehoach_ThucHien_GetByNamKeHoach_SUM";
        public const string PROCEDURE_GET_BY_DV = "spKehoach_ThucHien_GetByDV";
        public const string PARAM_TABLE = "@Table";
        public const string PARAM_STATUS_ID = "@STATUS_ID";
        public const string PARAM_NHOMCHITIEU_ID = "@NHOMCHITIEU";
        public const string PARAM_NHOM_DON_VI_ID = "@NHOM_DON_VI_ID";
        public const string PARAM_MOC_KIEM_SOAT_ID = "@MOC_KIEM_SOAT_ID";

        public const string PARAM_FLAG = "@FLAG";
        public const string PARAM_NAM_KE_HOACH_ID = "@NAM_KE_HOACH_ID";
        public const string PARAM_DON_VI_ID = "@NHOM_CHI_TIEU";
        public const string PARAM_DON_VI = "@DON_VI_ID";
        public const string PARAM_CHI_TIEU = "@CHI_TIEU_ID";
        public const string PARAM_GIA_TRI = "@GIA_TRI";
        public KeHoachThucHienDAO(string connectionString, System.Type type):
            base(connectionString, type) { }

        public List<KeHoachThucHien> Getall(int namkh , int flag)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                return GetList<KeHoachThucHien>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> HuyChotKeHoachThucHien(int namkh, int mocks, int donvi)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_HUY_CHOT);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI, donvi));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoachThucHien> Getkehoachthrieng(int namkh, int donviid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_Rieng_Getall_by_nkh);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI, donviid));
                return GetList<KeHoachThucHien>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoachThucHien> Getkehoachbymockiemsoat(int namkh, int mockiemsoat,int nhomdonvi, int flag)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_Rieng_Getall_by_mockiemsoat);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdonvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                return GetList<KeHoachThucHien>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<KeHoachThucHien> GetKHTHbyOption(int namkh, int mockiemsoat, int donvi, int chitieu)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_KHTH_BY_OPTION);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI, donvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU, chitieu));
                return GetList<KeHoachThucHien>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<KeHoachThucHien> GetallByDV(int namkh,int dv)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_DV);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI, dv));
                return GetList<KeHoachThucHien>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<SumKH> GetSumKehoachthuchien(int namkh, int flag)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_SUM);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                return GetList<SumKH>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<SUMCAP1> GetSumCAP1Kehoachthuchien(int namkh)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CAP1_GET_SUM);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                return GetList<SUMCAP1>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public bool Save(List<KeHoachThucHien> data)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT);
                DataTable dataTable = GetDataTable(data);
                cmd.Parameters.Add(new SqlParameter(PARAM_TABLE, dataTable));

                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch
            {                
                throw;
            }
        }

        public int DeleteKehoachthuchienbyNKH(int namkhid, int status, int nhomchitieu, int nhomdonvi,int mockiemsoat)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE_BY_NAMKEHOAH);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOMCHITIEU_ID, nhomchitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_NHOM_DON_VI_ID, nhomdonvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }
        public int InsertKHTHbyOption(int namkhid, int chitieu, int donvi, int mockiemsoat,int flag,string giatri )
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT_BY_OPTION);
                cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkhid));
                cmd.Parameters.Add(new SqlParameter(PARAM_CHI_TIEU, chitieu));
                cmd.Parameters.Add(new SqlParameter(PARAM_DON_VI, donvi));
                cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                cmd.Parameters.Add(new SqlParameter(PARAM_FLAG, flag));
                cmd.Parameters.Add(new SqlParameter(PARAM_GIA_TRI, giatri));
                return ExecuteNonQuery(cmd);
            }
            catch
            {
                throw;
            }
        }

        public List<KeHoachThucHien> Getallbynamkh(int namkh, int status, int mockiemsoat)
        {
            SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NKH);
            cmd.Parameters.Add(new SqlParameter(PARAM_NAM_KE_HOACH_ID, namkh));
            cmd.Parameters.Add(new SqlParameter(PARAM_STATUS_ID, status));
            cmd.Parameters.Add(new SqlParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
            return GetList<KeHoachThucHien>(cmd);
        }

        public int CheckChotbymockiemsoat(int namkh, int donviid, int mockiemsoat)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHECK_CHOT_MOC_KIEM_SOAT);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI, donviid));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                return Convert.ToInt32(ExecuteScalar(cmd));
            }
            catch
            {
                throw;
            }
        }
        public int CheckFeedBack(int namkh, int donviid, int mocks)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CheckFeedBack);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI, donviid));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
                return Convert.ToInt32(ExecuteScalar(cmd));
            }
            catch
            {
                throw;
            }
        }

        public List<ListChiTieu> CheckChot(int namkh, int status, int donviid, int mockiemsoat)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_CHECK_CHOT);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_STATUS_ID, status));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donviid));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mockiemsoat));
                return GetList<ListChiTieu>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public bool Update(List<KeHoachThucHien> data)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PARAM_TABLE);
                DataTable dataTable = GetDataTable(data);
                cmd.Parameters.Add(new SqlParameter(PARAM_TABLE, dataTable));

                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch 
            {                
                throw;
            }
        }

        private DataTable GetDataTable(List<KeHoachThucHien> data)
        {
            DataTable table = new DataTable();
            table.Columns.Add("KH_THUCHIEN_ID");
            table.Columns.Add("NAM_KE_HOACH_ID");
            table.Columns.Add("DON_VI_ID");
            table.Columns.Add("CHI_TIEU_ID");
            table.Columns.Add("GIA_TRI");
            table.Columns.Add("MOC_KIEM_SOAT_ID");
            table.Columns.Add("NGUOI_DUNG_ID");
            table.Columns.Add("LASTUPDATED");
            table.Columns.Add("STATUS_ID");
            table.Columns.Add("IS_CURRENT");
          
            foreach(var item in data)
            {
                table.Rows.Add(item.KH_THUC_HIEN_ID,
                               item.NAM_KE_HOACH_ID,
                               item.DON_VI_ID,
                               item.CHI_TIEU_ID,
                               item.GIA_TRI,
                               item.MOC_KIEM_SOAT_ID,
                               item.NGUOI_DUNG_ID,
                               DateTime.Today,
                               item.STATUS_ID,
                               item.IS_CURRENT);                
            }

            return table;
        }
    }
}
